﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;

namespace Sample_App_WS
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]

    public class Service1 : System.Web.Services.WebService
    {
        string db_values;
        int[] db_rollnum;

        [WebMethod]
        public string GetData(int value)
        {

            var dbNorthwind = new Sample_App_WSDataContext();
            var query = from c in dbNorthwind.Sample_Apps
                        where c.Roll_Number == value
                        select c;

            foreach (var c in query)
            {
                db_values = c.Age.Trim() + "," + c.First_Name + "," + c.Last_Name + "," + c.Marks_Obtained.Trim() + "," + c.Roll_Number;
            }

            return db_values;
        }

        [WebMethod]
        public int[] GetRollNumbers()
        {

            var dbNorthwind = new Sample_App_WSDataContext();
            var query = from c in dbNorthwind.Sample_Apps
                        select c;
            //query.Count();
            db_rollnum = new int[query.Count()];
            int count = 0;
            foreach (var c in query)
            {
                db_rollnum[count] = Convert.ToInt16(c.Roll_Number);
                count = count + 1;
            }

            return db_rollnum;
        }



        [WebMethod]
        public void Enroll_Student(string f_name, string l_name, int age, int mo)
        {
            var dbNorthwind = new Sample_App_WSDataContext();
            Sample_App tb_sample_app = new Sample_App();
            tb_sample_app.Age = age.ToString();
            tb_sample_app.First_Name = f_name;
            tb_sample_app.Last_Name = l_name;
            tb_sample_app.Marks_Obtained = mo.ToString();
            dbNorthwind.Sample_Apps.InsertOnSubmit(tb_sample_app);
            dbNorthwind.SubmitChanges();


        }

        // Get if student is adult or not (age > 18 == True else False)  Author : Gunjan

        string db_age;
        int age;
        string result;


        [WebMethod]
        public string GetAdultorNot(string Age)
        {

            var dbNorthwind = new Sample_App_WSDataContext();
            var query = from c in dbNorthwind.Sample_Apps
                        where c.Age == Age
                        select c;

            foreach (var c in query)
            {
                db_age = c.Age.Trim();
                age = Convert.ToInt32(db_age);

                if (age >= 18)
                {
                    result = c.AdultOrNot;
                }
                else
                {
                    result = c.AdultOrNot;
                }

            }

            return result;

        }

        // Get student's age only   Author : Gunjan

        string db_age1;
        // int[] db_rollnum;


        [WebMethod]
        public string GetAge(int RollNum)
        {

            var dbNorthwind = new Sample_App_WSDataContext();
            var query = from c in dbNorthwind.Sample_Apps
                        where c.Roll_Number == RollNum
                        select c;

            foreach (var c in query)
            {
                db_age1 = c.Age;
            }

            return db_age1;

        }

        [WebMethod]
        public DataSet GetXML(int RollNum)
        {

            var dbNorthwind = new Sample_App_WSDataContext();
            var query = from c in dbNorthwind.Sample_Apps
                        where c.Roll_Number == RollNum
                        select c;
            int Rollnum = RollNum;

            SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Sample_AppConnectionString"].ConnectionString);
            string sql = @"select * from Sample_App where Roll_number = ' " + RollNum + "'";

            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;

        }
    }
}
